package no.ntnu.fp.storage.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import no.ntnu.fp.handlers.exceptions.DatabaseException;
import no.ntnu.fp.model.Employee;
import no.ntnu.fp.model.Meeting;
import no.ntnu.fp.model.Notice;
import no.ntnu.fp.model.Notice.NoticeType;
import no.ntnu.fp.server.ConnectionProvider;
import no.ntnu.fp.storage.NoticeStorage;

public class NoticeStorageImpl implements NoticeStorage {

	private final MeetingStorageImpl meetingStorageImpl;
	private final EmployeeStorageImpl employeeStorageImpl;
	
	public NoticeStorageImpl(MeetingStorageImpl meetingStorageImpl,
							EmployeeStorageImpl employeeStorageImpl) {
		this.meetingStorageImpl = meetingStorageImpl;
		this.employeeStorageImpl = employeeStorageImpl;
	}

	public List<Notice> getUnreadNoticesByEmployee(Employee employee) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"SELECT * FROM Notice "  + 
					"WHERE employeeId = ? " +
					"AND isRead <> 1");
			stmt.setLong(1, employee.getId());
			
			ResultSet resultSet = stmt.executeQuery();
			
			List<Notice> notices = new ArrayList<Notice>();
			while (resultSet.next()) {
				notices.add(getCurrentNotice(resultSet, connection, employee));
			}
			
			stmt.close();
			
			return notices;
		} catch (SQLException e) {
			throw new DatabaseException("Could not get notices for employee", e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	private Notice getCurrentNotice(ResultSet resultSet, Connection connection,
			Employee employee) throws SQLException {
		long noticeId = resultSet.getLong("noticeId");
		
		long employeeId = resultSet.getLong("employeeId");
		if (employee == null || employee.getId() != employeeId) {
			employee = employeeStorageImpl.getEmployee(employeeId, connection);
		}
		
		long appointmentId = resultSet.getLong("appointmentId");
		Meeting meeting = meetingStorageImpl.getMeetingById(
				appointmentId, connection);
		
		String description = resultSet.getString("description");
		
		String noticeTypeString = resultSet.getString("noticeType") ;
		NoticeType noticeType = NoticeType.valueOf(noticeTypeString);
		
		boolean isRead = resultSet.getBoolean("isRead");
		
		return new Notice(noticeId, employee, meeting, noticeType, 
				description, isRead);
	}

	@Override
	public Notice markAsRead(Notice notice) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"UPDATE Notice " +
					"SET isRead = 1 " +
					"WHERE noticeId = ?");
			
			stmt.setLong(1, notice.getId());
			
			stmt.executeUpdate();
			
			stmt.close();
			
			return getById(notice.getId(), connection);
		} catch (SQLException e) {
			throw new DatabaseException("Could either not update notice " +
					"or retrieve " + notice, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}
	
	private Notice getById(long noticeId, Connection connection) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"SELECT * FROM Notice " +
				"WHERE noticeId = ?");
		
		stmt.setLong(1, noticeId);
		
		ResultSet resultSet = stmt.executeQuery();
		if (resultSet.next()) {
			Notice notice = getCurrentNotice(resultSet, connection, null);
			stmt.close();
			return notice;
		} else {
			stmt.close();
			throw new SQLException("Could not get notice with id = #" + noticeId);
		}
	}

	@Override
	public Notice addNotice(Notice notice) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"INSERT INTO Notice " +
					"VALUES(DEFAULT, ?, ?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			
			stmt.setLong(1, notice.getEmployee().getId());
			stmt.setLong(2, notice.getMeeting().getId());
			stmt.setString(3, notice.getDescription());
			stmt.setString(4, notice.getNoticeType().toString());
			stmt.setBoolean(5, notice.isRead());
			
			stmt.executeUpdate();
			
			ResultSet keys = stmt.getGeneratedKeys();
			if (keys.next()) {
				long noticeId = keys.getLong(1);
				notice = getById(noticeId, connection);
			} else {
				stmt.close();
				throw new SQLException("No keys generated for notice " + notice);
			}
			
			stmt.close();
			
			return notice;
		} catch (SQLException e) {
			throw new DatabaseException("Could not add notice " + notice, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}
}
